PostgreSQL
Parameters tuned
The parameters tuned by DBtune changed in respect to the tuning mode. Below are the three tuning modes we support.
Reload-only tuning mode
This tuning mode tunes without restarting the database and using reload only. This does not cause any downtime and is suitable for production environments. The following parameters are tuned in this mode:
work_mem
random_page_cost
seq_page_cost
checkpoint_completion_target
effective_io_concurrency
max_parallel_workers_per_gather
max_parallel_workers
max_wal_size
min_wal_size
bgwriter_lru_maxpages
bgwriter_delay
Restart tuning mode
Most of our user base prefer reload-only tuning to avoid disruptions in production. If your system can handle restarts gracefully and you want to tune more parameters, you can chose this mode which restarts the database up to 30 times in a span of few hours and provide you with additional performance. The following parameters are tuned in this mode in addition to the parameters tuned in the reload-only mode:
shared_buffers
max_worker_processes
Restart-once tuning mode
The reload-only tuning mode provides an additional pre-flight system check to ensure the system is in a healthy state and performing defaults. These checks are happening only at the beginning of the tuning session. The checks and corresponding suggestions (that will require only one restart) are as follows:
- Shared Buffers
- Check: Verify if
shared_buffers
is less than25%
of total memory. - Suggestion: Adjust
shared_buffers
to25%
of total memory.
Rationale: Allocating 25% of the system RAM to shared buffers is recommended by the official PostgreSQL documentation. This setting is generally sufficient for efficient operations, balancing memory use between PostgreSQL and other system processes. Only in edge cases, such as when the entire database fits in memory, might different values yield significant improvements. For more information, refer to the PostgreSQL documentation.
References: PostgreSQL.org, EnterpriseDB, pgAnalyze.
- Max Worker Processes
- Check: Verify if
max_worker_processes
, is less than 2 times the CPUs. - Suggestion: Adjust
max_worker_processes
to the following value:max(2 * num_of_cpus + free_worker, 8 + free_workers)
.
Thefree_workers
is calculated by the formulafree_workers = max_worker_processes - max_parallel_workers
.
Rationale: The formula for max_worker_processes
ensures that the parameter is large enough to handle the various processes efficiently without being excessive. This balance helps maintain system stability and performance under normal operational loads.
How will the suggestion be recommended and applied?
The DBtune agent will calculate the suggested values for the parameters and recommend applying them with a restart. You can then choose to apply the suggestion or not within the DBtune agent process. The suggestion will happen only once when you start the agent.
![DBtune restart once](/assets/images/restart-once-020e6bbbaedb2512ac5d408c1585f17a.png)
Optimization objectives
Average Query Runtime
Average query runtime represents latency, which is measured in milliseconds ms
.
DBtune computes this using the calls
and total_exec_time
columns from the pg_stat_statements
table.
Throughput
Throughput is defined as the number of transactions that the database completes successfully.
DBtune computes PostgreSQL's throughput from the xact_commit
metric in the pg_stat_database
statistics table.
Client monitoring stats
DBtune only retrieves performance metrics from the database and does not access or transmit any sensitive data, e.g., metadata and the tables data are not transferred.
System monitoring (posted every second)
Below is the data DBtune collects and sends to the DBtune server every second:
Collected data
Category | Subcategory |
---|---|
CPU stats | cpu_util |
Memory stats | free |
slab | |
used | |
total | |
active | |
cached | |
shared | |
buffers | |
percent | |
inactive | |
available | |
IO stats | busy_time |
read_time | |
read_bytes | |
read_count | |
write_time | |
write_bytes | |
write_count | |
read_merged_count | |
write_merged_count | |
DB Stats | Throughput |
Query Runtime |
System information (posted when the agent starts)
In less frequent intervals DBtune fetches the following data to understand the specifications of the system:
Collected data
Category | Subcategory |
---|---|
Hardware | Number of CPUs |
Total memory | |
Available memory | |
Cloud provider | |
Instance type | |
Disk type | |
Software | Database version |
Operating system type | |
Maximum connections | |
Database size |
To fetch the hardware information such as number of CPUs, total memory, and available memory, we utilize Python's psutil
library.
Custom methods were developed to retrieve cloud provider, instance type, and disk type. The software information operating system type is retrieved using a Python library named platform
.
Database version and max connections are obtained by querying the database system directly, i.e., SHOW server_version
and SHOW max_connections
. A custom method is implemented to fetch disk size.